-- 交易主题插入数据
-- 开启本地模式
set hive.exec.mode.local.auto=true;

-- 使用订单详情表关联商品信息维表，获取店铺ID
with t1 as (
    select a.order_id,
           a.product_id,
           a.product_cnt,
           a.product_price,
           a.weighing_cost,
           a.is_activity,
           b.supplier_code
    from ods_nshop.ods_02_order_detail a
             join ods_nshop.dim_pub_product b
                  on a.product_id = b.product_code
    where from_unixtime(cast(a.order_detail_ctime / 1000 as int), 'yyyyMMdd') = '20191104'
),
-- 统计订单支付次数
     t2 as (
         select order_id,
                count(*) pay_count
         from ods_nshop.ods_02_orders_pay_records
         where from_unixtime(cast(pay_ctime / 1000 as int), 'yyyyMMdd') = '20191104'
         group by order_id
     )
insert
overwrite
table
dwd_nshop.dwd_nshop_orders_details
partition
(
bdp_day = '${yesterday}'
)
-- 将数据进行关联
select a.order_id,
       a.order_status,
       t1.supplier_code,
       t1.product_id,
       a.customer_id,
       a.consignee_zipcode,
       a.pay_type,
       a.pay_nettype,
       t2.pay_count,
       t1.product_price,
       t1.product_cnt,
       t1.weighing_cost,
       a.district_money,
       a.shipping_money,
       a.payment_money,
       t1.is_activity,
       a.order_ctime
from ods_nshop.ods_02_orders a
         join t1 on a.order_id = t1.order_id
         join t2 on a.order_id = t2.order_id
where from_unixtime(cast(a.order_ctime / 1000 as int), 'yyyyMMdd') = '20191104';




